package com.ubst.smartad.impl;

import java.sql.*;
import java.util.*;
import java.io.*;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Vector;

import com.ubst.smartad.dao.TSAUserDO;
import com.ubst.smartad.helper.CryptHelper;
import com.ubst.smartad.common.TimeUtil;
import com.ubst.smartad.logger.Debug;

public class TSAUserMngImpl {
	
	Connection con = null;
	private CryptHelper chelper;
	private String serverName;
	private Debug debug;

	/** 
     * 생성자
     *   
     *   
    */ 
	public TSAUserMngImpl(Connection con, String serverName, String log_level) {
		this.con = con;
		this.chelper = new CryptHelper();
		this.serverName = serverName;
		this.debug = new Debug("TSAUserMngImpl", log_level);

	}
	
	/**
	 * @author : KJH 
	 * 관리자 검증.
	 *
	 */
	public TSAUserDO isValidAdmin(String userid, String passwd) throws SQLException {

		PreparedStatement pstmt=null;
		ResultSet rs = null;

		TSAUserDO user_info = null;

		try {
			
			CryptHelper cryptHelper = new CryptHelper();


			StringBuffer qrybuf = new StringBuffer();
			qrybuf.append("SELECT ")
				.append("  USERNAME ,USERLEVEL ,DELEUSERID ,DELESDATE ,DELEEDATE ")
				.append(" ,UCOMPANY ,UDEPARTMENT ,UPOSITION ,UMAILADDR ,UMOBILE ")
				.append(" ,USERDN ,UADDRTYPE ,USERIP ,LASTLOGIN ,CREATEDATE ")
				.append(" FROM TSA_USER ")
				.append(" WHERE USERID=? ")
				.append(" AND PASSWD=? ")
				.append(" AND USERLEVEL IN ('1','2','3','4') ")
				.append(" AND NSTATUS=1 ");

			pstmt = con.prepareStatement( qrybuf.toString() );
			pstmt.setString(1, userid);
			pstmt.setString(2, cryptHelper.encrypt(passwd));

			rs = pstmt.executeQuery();

			if ( rs.next() ){
				user_info = new TSAUserDO();
				user_info.userid = userid;
				user_info.username = rs.getString("USERNAME");
				user_info.userlevel = rs.getString("USERLEVEL");
				user_info.deleuserid = rs.getString("DELEUSERID");
				user_info.delesdate = rs.getString("DELESDATE");
				user_info.deleedate = rs.getString("DELEEDATE");
				user_info.ucompany = rs.getString("UCOMPANY");
				user_info.udepartment = rs.getString("UDEPARTMENT");
				user_info.uposition = rs.getString("UPOSITION");
				user_info.umailaddr = rs.getString("UMAILADDR");
				user_info.umobile = rs.getString("UMOBILE");
				user_info.userdn = rs.getString("USERDN");
				user_info.uaddrtype = rs.getString("UADDRTYPE");
				user_info.userip = rs.getString("USERIP");
				user_info.lastlogin = rs.getString("LASTLOGIN");
				user_info.createdate = rs.getString("CREATEDATE");
			}

			rs.close();
			pstmt.close();

		}catch (SQLException e){
			throw e;
		}finally{
			if ( rs != null) try{rs.close();}catch(SQLException ee){}
			if ( pstmt != null) try{pstmt.close();}catch(SQLException ee){}
		}

		return user_info;
	}

	/** 
	 * @author KJH
     * 계정관리 사용자 등록
     *   
     */  
    public void insertUser(TSAUserDO userDO) throws SQLException{
        PreparedStatement pstmt=null;

        StringBuffer queryBuf = null;
        try {

			long l_cur_date = TimeUtil.getCurrentTime();

            queryBuf = new StringBuffer();
			queryBuf.append("INSERT INTO TSA_USER " )
				.append(" ( ")
				.append("  USERID ,UGROUPID ,PASSWD ,OLDPASSWD ,USERNAME ")
				.append(" ,USERLEVEL ,DELEUSERID ,DELESDATE ,DELEEDATE ,FAILCNT ")
				.append(" ,UCOMPANY ,UDEPARTMENT ,UPOSITION ,UMAILADDR ,UMOBILE ")
				.append(" ,USERDN ,UADDRTYPE ,USERIP ,NSTATUS ,LASTLOGIN ")
				.append(" ,CREATEDATE ")
				.append(" ) ")
				.append(" VALUES")
				.append(" ( ") 
				.append("  ? ,? ,? ,? ,? ")
				.append(" ,? ,? ,? ,? ,? ")
				.append(" ,? ,? ,? ,? ,? ")
				.append(" ,? ")
				.append(" ) ");

			debug.debug("insertUser ==> "+queryBuf.toString());

			pstmt = con.prepareStatement( queryBuf.toString() );
			int idx = 1;
			pstmt.setString(idx++, userDO.userid);
			pstmt.setString(idx++, userDO.ugroupid);
			pstmt.setString(idx++, chelper.encrypt(userDO.passwd));
			pstmt.setString(idx++, userDO.oldpasswd);
			pstmt.setString(idx++, userDO.username);
			pstmt.setString(idx++, userDO.userlevel);
			pstmt.setString(idx++, userDO.deleuserid);
			pstmt.setString(idx++, userDO.delesdate);
			pstmt.setString(idx++, userDO.deleedate);
			pstmt.setInt(idx++, userDO.failcnt);
			pstmt.setString(idx++, userDO.ucompany);
			pstmt.setString(idx++, userDO.udepartment);
			pstmt.setString(idx++, userDO.uposition);
			pstmt.setString(idx++, userDO.umailaddr);
			pstmt.setString(idx++, userDO.umobile);
			pstmt.setString(idx++, userDO.userdn);
			pstmt.setString(idx++, userDO.uaddrtype);
			pstmt.setString(idx++, userDO.userip);
			pstmt.setInt(idx++, userDO.nstatus);
			pstmt.setString(idx++, userDO.lastlogin);
			pstmt.setString(idx++, TimeUtil.getDateString(l_cur_date, "yyyyMMddHHmmss"));

            pstmt.execute();
            pstmt.close();
			queryBuf = null;

        }catch (SQLException e){
			throw e;
        }finally{
            if ( pstmt != null) try{pstmt.close();}catch(SQLException ee){}
        }

    }

    /** 
	 * @author KJH
     * 사용자 정보 변경
     *   
     */  
    public void updateUser(TSAUserDO userDO) throws SQLException{
        PreparedStatement pstmt=null;

        StringBuffer queryBuf = null;
        try {

			long l_cur_date = TimeUtil.getCurrentTime();

            queryBuf = new StringBuffer();
			queryBuf.append("UPDATE TSA_USER " )
				.append(" SET ")
				.append("  UGROUPID=? ,OLDPASSWD=? ,USERNAME=? ,USERLEVEL=? ,DELEUSERID=? ")
				.append(" ,DELESDATE=? ,DELEEDATE=? ,FAILCNT=? ,UCOMPANY=? ,UDEPARTMENT=? ")
				.append(" ,UPOSITION=? ,UMAILADDR=? ,UMOBILE=? ,USERDN=? ,UADDRTYPE=? ")
				.append(" ,USERIP=? ,NSTATUS=? ,LASTLOGIN=? ");
			
			if(userDO.passwd!=null && !userDO.passwd.trim().equals(""))
			{
				queryBuf.append(" ,PASSWD=? ");
			}
			queryBuf.append(" WHERE USERID=? ");

			debug.debug("updateUser ==> "+queryBuf.toString());

			pstmt = con.prepareStatement( queryBuf.toString() );
			int idx = 1;
			pstmt.setString(idx++, userDO.ugroupid);
			pstmt.setString(idx++, userDO.oldpasswd);
			pstmt.setString(idx++, userDO.username);
			pstmt.setString(idx++, userDO.userlevel);
			pstmt.setString(idx++, userDO.deleuserid);
			pstmt.setString(idx++, userDO.delesdate);
			pstmt.setString(idx++, userDO.deleedate);
			pstmt.setInt(idx++, userDO.failcnt);
			pstmt.setString(idx++, userDO.ucompany);
			pstmt.setString(idx++, userDO.udepartment);
			pstmt.setString(idx++, userDO.uposition);
			pstmt.setString(idx++, userDO.umailaddr);
			pstmt.setString(idx++, userDO.umobile);
			pstmt.setString(idx++, userDO.userdn);
			pstmt.setString(idx++, userDO.uaddrtype);
			pstmt.setString(idx++, userDO.userip);
			pstmt.setInt(idx++, userDO.nstatus);
			pstmt.setString(idx++, userDO.lastlogin);
			if(userDO.passwd!=null && !userDO.passwd.trim().equals(""))
			{
				pstmt.setString(idx++, chelper.encrypt(userDO.passwd));
			}
			pstmt.setString(idx++, userDO.userid);

            pstmt.execute();
            pstmt.close();
			queryBuf = null;

        }catch (SQLException e){
			throw e;
        }finally{
            if ( pstmt != null) try{pstmt.close();}catch(SQLException ee){}
        }

    }

    /** 
	 * @author KJH
     * 사용자 최종로그인 시간등록
     *   
     */  
    public void updateLastLogin(String userid) throws SQLException{
        PreparedStatement pstmt=null;

        StringBuffer queryBuf = null;
        try {

			long l_cur_date = TimeUtil.getCurrentTime();

            queryBuf = new StringBuffer();
			queryBuf.append("UPDATE TSA_USER " )
				.append(" SET ")
				.append("  LASTLOGIN=? ")
				.append(" WHERE USERID=? ");

			debug.debug("updateLastLogin ==> "+queryBuf.toString());

			pstmt = con.prepareStatement( queryBuf.toString() );
			pstmt.setString(1, TimeUtil.getDateString(l_cur_date, "yyyyMMddHHmmss"));
			pstmt.setString(2, userid);

            pstmt.execute();
            pstmt.close();
			queryBuf = null;

        }catch (SQLException e){
			throw e;
        }finally{
            if ( pstmt != null) try{pstmt.close();}catch(SQLException ee){}
        }

    }

	/**
	 * @author : KJH 
	 * 사용자 정보 출력
	 *
	 */
	public TSAUserDO selectUser(String userid) throws SQLException {

		PreparedStatement pstmt=null;
		ResultSet rs = null;

		TSAUserDO userDO = null;

		try {
			
			StringBuffer queryBuf = new StringBuffer();
			queryBuf.append("SELECT ")
				.append("  UGROUPID ,PASSWD ,OLDPASSWD ,USERNAME ,USERLEVEL ")
				.append(" ,DELEUSERID ,DELESDATE ,DELEEDATE ,FAILCNT ,UCOMPANY ")
				.append(" ,UDEPARTMENT ,UPOSITION ,UMAILADDR ,UMOBILE ,USERDN ")
				.append(" ,UADDRTYPE ,USERIP ,NSTATUS ,LASTLOGIN ,CREATEDATE ")
				.append(" FROM TSA_USER ")
				.append(" WHERE USERID=? ");

			debug.debug("selectUser ==> "+queryBuf.toString());

			pstmt = con.prepareStatement( queryBuf.toString() );
			int idx = 1;
			pstmt.setString(idx++, userid);

			rs = pstmt.executeQuery();

			if ( rs.next() ){
				userDO = new TSAUserDO();
				userDO.userid = userid;
				userDO.ugroupid = rs.getString("UGROUPID");
				//userDO.passwd = rs.getString("PASSWD");
				//userDO.oldpasswd = rs.getString("OLDPASSWD");
				userDO.username = rs.getString("USERNAME");
				userDO.userlevel = rs.getString("USERLEVEL");
				userDO.deleuserid = rs.getString("DELEUSERID");
				userDO.delesdate = rs.getString("DELESDATE");
				userDO.deleedate = rs.getString("DELEEDATE");
				userDO.failcnt = rs.getInt("FAILCNT");
				userDO.ucompany = rs.getString("UCOMPANY");
				userDO.udepartment = rs.getString("UDEPARTMENT");
				userDO.uposition = rs.getString("UPOSITION");
				userDO.umailaddr = rs.getString("UMAILADDR");
				userDO.umobile = rs.getString("UMOBILE");
				userDO.userdn = rs.getString("USERDN");
				userDO.uaddrtype = rs.getString("UADDRTYPE");
				userDO.userip = rs.getString("USERIP");
				userDO.nstatus = rs.getInt("NSTATUS");
				userDO.lastlogin = rs.getString("LASTLOGIN");
				userDO.createdate = rs.getString("CREATEDATE");
			}

			rs.close();
			pstmt.close();

		}catch (SQLException e){
			throw e;
		}finally{
			if ( rs != null) try{rs.close();}catch(SQLException ee){}
			if ( pstmt != null) try{pstmt.close();}catch(SQLException ee){}
		}

		return userDO;
	}

	/**
	 * @author : KJH 
	 * 그룹에 포함된 사용자 리스트 출력
	 *
	 */
	public Vector selectUserOfGroup(String ugroupid) throws SQLException {

		PreparedStatement pstmt=null;
		ResultSet rs = null;

		Vector userDOList = new Vector();
		TSAUserDO userDO = null;

		try {
			
			StringBuffer queryBuf = new StringBuffer();
			queryBuf.append("SELECT ")
				.append("  USERID ,PASSWD ,OLDPASSWD ,USERNAME ,USERLEVEL ")
				.append(" ,DELEUSERID ,DELESDATE ,DELEEDATE ,FAILCNT ,UCOMPANY ")
				.append(" ,UDEPARTMENT ,UPOSITION ,UMAILADDR ,UMOBILE ,USERDN ")
				.append(" ,UADDRTYPE ,USERIP ,NSTATUS ,LASTLOGIN ,CREATEDATE ")
				.append(" FROM TSA_USER ")
				.append(" WHERE UGROUPID=? ");

			debug.debug("selectUserOfGroup ==> "+queryBuf.toString());

			pstmt = con.prepareStatement( queryBuf.toString() );
			int idx = 1;
			pstmt.setString(idx++, ugroupid);

			rs = pstmt.executeQuery();

			while ( rs.next() ){
				userDO = new TSAUserDO();
				userDO.userid = rs.getString("USERID");
				userDO.ugroupid = ugroupid;
				//userDO.passwd = rs.getString("PASSWD");
				//userDO.oldpasswd = rs.getString("OLDPASSWD");
				userDO.username = rs.getString("USERNAME");
				userDO.userlevel = rs.getString("USERLEVEL");
				userDO.deleuserid = rs.getString("DELEUSERID");
				userDO.delesdate = rs.getString("DELESDATE");
				userDO.deleedate = rs.getString("DELEEDATE");
				userDO.failcnt = rs.getInt("FAILCNT");
				userDO.ucompany = rs.getString("UCOMPANY");
				userDO.udepartment = rs.getString("UDEPARTMENT");
				userDO.uposition = rs.getString("UPOSITION");
				userDO.umailaddr = rs.getString("UMAILADDR");
				userDO.umobile = rs.getString("UMOBILE");
				userDO.userdn = rs.getString("USERDN");
				userDO.uaddrtype = rs.getString("UADDRTYPE");
				userDO.userip = rs.getString("USERIP");
				userDO.nstatus = rs.getInt("NSTATUS");
				userDO.lastlogin = rs.getString("LASTLOGIN");
				userDO.createdate = rs.getString("CREATEDATE");

				userDOList.add(userDO);
				userDO = null;
			}

			rs.close();
			pstmt.close();

		}catch (SQLException e){
			throw e;
		}finally{
			if ( rs != null) try{rs.close();}catch(SQLException ee){}
			if ( pstmt != null) try{pstmt.close();}catch(SQLException ee){}
		}

		return userDOList;
	}

    /** 
	 * @author KJH
     * 그룹에 포함된 사용자 삭제
     *   
     */  
    public void deleteUserOfGroup(String ugroupid) throws SQLException{
        PreparedStatement pstmt=null;

        StringBuffer queryBuf = null;
        try {

            queryBuf = new StringBuffer();
			queryBuf.append("DELETE FROM TSA_USER " )
				.append(" WHERE UGROUPID=? ");

			debug.debug("deleteUserOfGroup ==> "+queryBuf.toString());

			pstmt = con.prepareStatement( queryBuf.toString() );
			int idx = 1;
			pstmt.setString(idx++, ugroupid);

            pstmt.execute();
            pstmt.close();
			queryBuf = null;

        }catch (SQLException e){
			throw e;
        }finally{
            if ( pstmt != null) try{pstmt.close();}catch(SQLException ee){}
        }

    }

    /** 
	 * @author KJH
     * 사용자 삭제
     *   
     */  
    public void deleteUser(String userid) throws SQLException{
        PreparedStatement pstmt=null;

        StringBuffer queryBuf = null;
        try {

            queryBuf = new StringBuffer();
			queryBuf.append("DELETE FROM TSA_USER " )
				.append(" WHERE USERID=? ");

			debug.debug("deleteUser ==> "+queryBuf.toString());

			pstmt = con.prepareStatement( queryBuf.toString() );
			int idx = 1;
			pstmt.setString(idx++, userid);

            pstmt.execute();
            pstmt.close();
			queryBuf = null;

        }catch (SQLException e){
			throw e;
        }finally{
            if ( pstmt != null) try{pstmt.close();}catch(SQLException ee){}
        }

    }

}
